using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using Database;
namespace Database
{
   public class MayTinh
    {
        private int mamay;

        public int Mamay
        {
            get { return mamay; }
            set { mamay = value; }
        }
        private string tenmay;

        public string Tenmay
        {
            get { return tenmay; }
            set { tenmay = value; }
        }
        private double giaban;

        public double Giaban
        {
            get { return giaban; }
            set { giaban = value; }
        }
        private int soluong;

        public int Soluong
        {
            get { return soluong; }
            set { soluong = value; }
        }
        private int mahangsx;

        public int Mahangsx
        {
            get { return mahangsx; }
            set { mahangsx = value; }
        }
        private string mota;

        public string Mota
        {
            get { return mota; }
            set { mota = value; }
        }
       
        private string hinhanh;
        private int loaimt;

        public int Loaimt
        {
            get { return loaimt; }
            set { loaimt = value; }
        }
        public string Hinhanh
        {
            get { return hinhanh; }
            set { hinhanh = value; }
        }
        private bool hienthi;

        public bool Hienthi
        {
            get { return hienthi; }
            set { hienthi = value; }
        }
       public MayTinh()
       {
       }
       public MayTinh(int mamay, string tenmay, float giaban, int soluong, int mahangsx, string mota,int loaimt, string hinhanh, bool hienthi)
       {
           this.mamay = mamay;
           this.tenmay = tenmay;
           this.giaban = giaban;
           this.soluong = soluong;
           this.mahangsx = mahangsx;
           this.mota = mota;
           this.loaimt = loaimt;
           this.hinhanh = hinhanh;
           this.hienthi = hienthi;
       }
       public static DataSet DSSP()
       {
           DataSet ds = new DataSet();
           string query = "select mt.mamay,mt.tenmay,mt.giaban,mt.soluong,hsx.tenhang from maytinh mt, hangsx hsx where mt.mahangsx=hsx.mahangsx";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query,db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public static DataSet DSMaMay()
       {
           DataSet ds = new DataSet();
           string query = "select mamay,tenmay from maytinh";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public static DataSet DSMaHangSX()
       {
           DataSet ds = new DataSet();
           string query = "select mahangsx from maytinh";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public DataSet DSMayTinh()
       {
           DataSet ds = new DataSet();
           string query = "select * from maytinh";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public static DataSet DSLoaiMT()
       {
           DataSet ds = new DataSet();
           string query = "select distinct loaimt from maytinh";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public bool ThemMT(string tenmay, float giaban, int soluong, int mahangsx, string mota,int loaimt,string hinhanh,bool hienthi )
       {
           bool kq = true;

           try
           {
               string query = "insert into MayTinh(tenmay,giaban,soluong,mahangsx,mota,loaimt,hinhanh,hienthi) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
               query = String.Format(query, tenmay, giaban, soluong, mahangsx, mota,loaimt,hinhanh,hienthi);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query,db.GetConnect());
               return cmd.ExecuteNonQuery()>0;
           }
           catch
           {
               kq = false;
           }

           return kq;
       }
       public bool XoaMT(int mamay)
       {
           bool kq = true;

           try
           {
               string query = "Delete MayTinh where mamay='{0}'";
               query = String.Format(query,mamay);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query, db.GetConnect());
               cmd.ExecuteNonQuery();
           }
           catch
           {
               kq = false;
           }

           return kq;
       }
       public bool CapNhatMT(int mamay,string tenmay, float giaban, int soluong, int mahangsx, string mota, int loaimt, string hinhanh, bool hienthi)
       {
           bool kq = true;

           try
           {
               string query = "update MayTinh set tenmay='{0}',giaban='{1}',soluong='{2}',mahangsx='{3}',mota='{4}',loaimt='{5}',hinhanh='{6}',hienthi='{7}' where mamay='{8}'";
               query = String.Format(query, tenmay,giaban,soluong,mahangsx,mota,loaimt,hinhanh,hienthi,mamay);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query,db.GetConnect());
              return cmd.ExecuteNonQuery()>0;
           }
           catch
           {
               kq = false;
           }

           return kq;
       }
       public MayTinh ThongTinMT(int mamay)
       {
           string query = "select * from MayTinh where mamay=" +mamay;
           ConnectToDatabase db = new ConnectToDatabase();
           SqlCommand sc = new SqlCommand(query, db.GetConnect());
           SqlDataReader reader = sc.ExecuteReader();
           MayTinh mt = new MayTinh();
           while (reader.Read())
           {

               mt.mamay = reader.GetInt32(0);
               mt.tenmay = reader.GetString(1);
               mt.giaban = reader.GetDouble(2);
               mt.soluong = reader.GetInt32(3);
               mt.mahangsx = reader.GetInt32(4);
               mt.mota = reader.GetString(5);
               mt.loaimt = reader.GetInt32(6);
               mt.hinhanh = reader.GetString(7);
               mt.hienthi = reader.GetBoolean(8);

           }
           return mt;
       }
       public DataTable TimKiemSanPham(string tenmay, double giatu, double giaden)
       {
           DataTable ds = new DataTable();
           try
           {
               string query = "select mt.* from maytinh mt where " + giatu + "<=giaban and giaban<=" + giaden + " and tenmay like '%" + tenmay + "%' and mt.hienthi = 'true' ";

               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch (Exception ex)
           {
               Console.WriteLine(ex.Message);
           }
           return ds;
       }

       public DataTable TimKiemSanPhamTheoGia(double giatu, double giaden)
       {
           DataTable ds = new DataTable();
           try
           {
               string query = "select mt.* from maytinh mt where " + giatu + "<=giaban and giaban <= " + giaden + " and mt.hienthi = 'true' ";
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch (Exception ex)
           {
               Console.WriteLine(ex.Message);
           }
           return ds;
       }


       public DataTable TimKiemSanPhamTheoTen(string tenmay)
       {
           DataTable ds = new DataTable();
           try
           {
               string query = "select mt.* from maytinh mt where tenmay like'%" + tenmay + "%' and mt.hienthi = 'true'";

               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch (Exception ex)
           {
               Console.WriteLine(ex.Message);
           }
           return ds;
       }
       public DataTable DSMT()
       {
           DataTable ds = new DataTable();
           try
           {
               string query = "select mt.mamay,mt.tenmay,mt.giaban,mt.soluong,mt.mahangsx,mt.mota,mt.Loaimt,mt.hinhanh,mt.hienthi"
                                   + " from maytinh mt,hangsx hx"
                                   + " where mt.mahangsx=hx.mahangsx and mt.hienthi = 'true'";
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch
           {
               ds = null;
           }
           return ds;
       }
       public DataTable DSMTBC()
       {
           DataTable ds = new DataTable();
           try
           {
               string query = "select top 9 * from maytinh order by soluong DESC";
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch
           {
               ds = null;
           }
           return ds;
       }
       public int LayTongSoSP()
       {
           string query = " select count (*) from maytinh where hienthi = 'true'";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlCommand cmd = new SqlCommand(query, db.GetConnect());
           int total = (int)cmd.ExecuteScalar();
           db.CloseConnect();
           return total;
       }
       public int LayTongSoSPTheoHangSX(int mahangsx)
       {
           string query = " select count (*) from maytinh where mahangsx = {0} and hienthi = 'true'";
           query = string.Format(query, mahangsx);
           ConnectToDatabase db = new ConnectToDatabase();
           SqlCommand cmd = new SqlCommand(query, db.GetConnect());
           int total = (int)cmd.ExecuteScalar();
           db.CloseConnect();
           return total;
       }
       public DataSet DanhSachSPTheoHangSX(int mahangsx)
       {
           DataSet ds = new DataSet();
           try
           {
               string query = "select * from maytinh where mahangsx = {0} and hienthi = 'true'";
               query = string.Format(query, mahangsx);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch
           {
               ds = null;
           }
           return ds;
       }
       public DataTable DSMTTHSX(int mahangsx)
       {
           DataTable ds = new DataTable();
           try
           {
               string query = "select * from maytinh where mahangsx = {0} and hienthi = 'true'";
               query = string.Format(query, mahangsx);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch
           {
               ds = null;
           }
           return ds;
       }
       public DataSet MayTinhCaoCap()
       {
           DataSet ds = new DataSet();
           try
           {
               string query = "select top 1 * from maytinh where giaban>=40000000 and loaimt=1";
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch
           { ds = null; }

           return ds;
       }
       public DataSet MayTinhTonKho()
       {
           DataSet ds = new DataSet();
           try
           {
               string query = "select top 1 * from maytinh where soluong>=10 and loaimt=1";
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch
           { ds = null; }

           return ds;
       }
       public DataTable MayTinhTheoMa(int mamay)
       {
           DataSet ds = new DataSet();
           try
           {
               string query = string.Format("select mt.*,hsx.tenhang from maytinh mt,hangsx hsx where mt.mahangsx=hsx.mahangsx and mt.hienthi = 'true' and mamay={0}", mamay);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch
           {
               ds = null;
           }
           return ds.Tables[0];
       }
    }
}
